﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
using System.Web.Services;
using System.Net.Mail;
using System.Web;
using System.Net;
namespace BAL
{
    public class Customers
    {
        public static string con = BAL.conn.connectionString;
        //public static int verifyuser(string username, string password, int login)
        //{
        //    SqlParameter[] arrParam = new SqlParameter[3];
        //    arrParam[0] = new SqlParameter("@username", SqlDbType.VarChar);
        //    arrParam[0].Value = username;
        //    arrParam[1] = new SqlParameter("@password", SqlDbType.VarChar);
        //    arrParam[1].Value = password;
        //    arrParam[2] = new SqlParameter("@login", SqlDbType.Int);
        //    arrParam[2].Value = login;
        //    int Val = Convert.ToInt32(CTEA_INDIA.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "CT_verifyuser", arrParam));
        //    return Val;
        //}
        //public static int OnForgotPassword(string username, int type)
        //{
        //    int intReturnValue = 0;
        //    int val = verifyuser(username, "", type);


        //    if (!(val.Equals(-1)))
        //    {
        //        string pass = "";
        //        string Info = "";
        //        string strName = "User";
        //        string emailID = "";

        //        SqlParameter[] arrParam = new SqlParameter[2];
        //        arrParam[0] = new SqlParameter("@username", SqlDbType.VarChar);
        //        arrParam[0].Value = username;
        //        arrParam[1] = new SqlParameter("@type", SqlDbType.Int);
        //        arrParam[1].Value = type;


        //        DataSet ds = CTEA_INDIA.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "CT_ForgotPassword", arrParam);
        //        pass = ds.Tables[0].Rows[0]["Password"].ToString();
        //        strName = ds.Tables[0].Rows[0]["Name"].ToString();
        //        emailID = ds.Tables[0].Rows[0]["emailid"].ToString();

        //        if (pass != "")
        //        {
        //            pass = BAL.Encryption.Decrypt(pass, "&%#@?,:*");
        //            Info = "<span style=\"font-size:12px;\"><br>" +
        //            "Dear <b>" + strName + "</b>,<br><br>Please find the details of your CTEA_INDIA account :" +
        //            "<br><br>Login Username: <b>" + username + "</b><br>Password: <b>" + pass + "</b><br><br>Best Regards," +
        //            "<br>CTEA_INDIA Team<br><a href=\"http://www.CTEAINDIA.in\">www.CTEAINDIA.in</a></span><br><br><img src=\"cid:companylogo\">";
        //            BAL.Common.SendEmail("", emailID, "Password Recovery", Info);
        //            intReturnValue = 1;
        //        }
        //    }
        //    return intReturnValue;
        //}
        //public static DataSet GetAllCandidate()
        //{
        //    DataSet ds = CTEA_INDIA.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "CT_GetPendingCandidate");
        //    return ds;
        //}
        //public static string[] GetCandidateProfile(int userid)
        //{
        //    SqlParameter[] arrParam = new SqlParameter[1];
        //    arrParam[0] = new SqlParameter("@CandidateID", SqlDbType.Int);
        //    arrParam[0].Value = userid;

        //    SqlDataReader sdr = CTEA_INDIA.DAL.SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "CT_GetCandidateProfile", arrParam);
        //    int count = sdr.FieldCount;
        //    string[] details = new string[count];
        //    while (sdr.Read())
        //    {
        //        for (int i = 0; i < count; i++)
        //        {
        //            details[i] = sdr[i].ToString();
        //        }
        //    }
        //    sdr.Close();
        //    return details;
        //}
        //public static string[] GetUserMasterProfile(int UserId)
        //{
        //    SqlParameter[] arrParam = new SqlParameter[1];

        //    arrParam[0] = new SqlParameter("@UserId", SqlDbType.Int);
        //    arrParam[0].Value = UserId;

        //    SqlDataReader sdr = CTEA_INDIA.DAL.SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "CT_GetUserMasterProfileById", arrParam);

        //    int count = sdr.FieldCount;
        //    string[] details = new string[count];

        //    while (sdr.Read())
        //    {
        //        for (int i = 0; i < count; i++)
        //        {
        //            details[i] = sdr[i].ToString();

        //        }
        //    }
        //    sdr.Close();
        //    return details;
        //}
        //public static void SendUserDetails(string To_Email_ID, string password)
        //{
        //    string host = HttpContext.Current.Request.Url.Host;
        //    string content = "<div style=\"margin-left:20px;\">" +
        //                     "<span style=\"font-size: 12px; font-family: 'CENTURY GOTHIC'\">" +
        //                     "<br>Dear <b>" + To_Email_ID + "</b>,<br><br>" +
        //                     "Welcome to <b>CTEA INDIA </b><br><br>" +
        //                     "We are pleased to welcome you to CTEA INDIA. Your account details are as mentioned below." +
        //                      "<br>UserID: " + To_Email_ID + "<br>Password: " + password +
        //                     "<br> Please change your password after login and keep this safely." +
        //                     "<br><br>Best Regards,<br>CTEA INDIA Team" +
        //                     "<br><a href=\"http://www.CTEAINDIA.in\">www.CTEAINDIA.in</a></span><br><br><img src=\"cid:companylogo\"></div>";
        //    string Subject = "User Details";
        //    BAL.Common.SendEmail("", To_Email_ID, Subject, content, 0);

        //}
        //public static void RegisterLink(string To_Email_ID, string UserId, string V_str, string Name)
        //{
        //    if (Name.Trim() == "") { Name = "User"; }
        //    if (Name != "") { Name = "<b>" + Name + "</b>"; }
        //    string host = HttpContext.Current.Request.Url.Host;
        //    string content = "<div style=\"margin-left:20px;\">" +
        //                     "<span style=\"font-size: 12px; font-family: 'CENTURY GOTHIC'\">" +
        //                     "<br>Dear " + Name + ",<br><br>" +
        //                     "Warm Welcome from <b>CTEA INDIA Team!!!</b><br> We thank you for showing continued interest in CTEA INDIA.<br>" +
        //                     "You are just one step away from becoming a registered member." +
        //                     "<br>Kindly click on the link below for email id verification :" +
        //                     "<br><br>url: <a href=\"http://" + host + "/Confirmation.aspx?id=" +
        //                     UserId + "&code=" + V_str + "\">Confirm Now</a>" +
        //                     "<br><br>Best Regards,<br>CTEA INDIA Team<br>" +
        //                     "<a href=\"http://www.cteaindia.in\">www.cteaindia.in</a></span><br><br><img src=\"cid:companylogo\" height=150 width=150></div>";


        //    string Subject = "Confirmation mail From CTEA INDIA";
        //    //LinkedResource imagelink = new LinkedResource(HttpContext.Current.Server.MapPath("images/logo.jpg"));
        //    //imagelink.ContentId = "imageId";
        //    //imagelink.TransferEncoding = System.Net.Mime.TransferEncoding.Base64;

        //    BAL.Common.SendEmail("", To_Email_ID, Subject, content, 0);
        //}

        public static void PopulateParticularsName(ref DropDownList ddlPName)
        {
            //objWsrComm = new WSRCommon.WSCommon();
            //DataSet ds = objWsrComm.GetCityList();
            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, "TS_GetPartclrName");
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    ddlPName.DataSource = ds.Tables[0];

                    ddlPName.DataTextField = "Name";
                    ddlPName.DataValueField = "Name";
                    ddlPName.DataBind();

                    ListItem lst = new ListItem("Other", "-1");
                    ddlPName.Items.Insert(ds.Tables[0].Rows.Count, lst);
                    //ListItem lst1 = new ListItem("--ALL Particulars Name--", "2");
                
                    //ddlPName.Items.Insert(1, lst1);

                    //foreach (ListItem _listItem in ddlPName.Items)
                    //{

                    //    _listItem.Attributes.Add("title", _listItem.Text + _listItem.Value);

                    //}

                }
                else
                {
                    ddlPName.Items.Clear();
                    //ListItem lst = new ListItem("----Select category----", "-1");
                    //ddlLocation.Items.Insert(0, lst);
                }
            }
        }

        public static void InsertProductDetails(string category, string ParticularsName,
            string BrandName, string ItemCode, string Pic, string Price, string Description, 
            string subcategory, string size, string vat, string fularge)
        {
            SqlParameter[] arrParam = new SqlParameter[11];

            arrParam[0] = new SqlParameter("@category", SqlDbType.VarChar);
            arrParam[0].Value = category;
            arrParam[1] = new SqlParameter("@ParticularsName", SqlDbType.VarChar);
            arrParam[1].Value = ParticularsName;
            arrParam[2] = new SqlParameter("@BrandName", SqlDbType.VarChar);
            arrParam[2].Value = BrandName;
            arrParam[3] = new SqlParameter("@ItemCode", SqlDbType.VarChar);
            arrParam[3].Value = ItemCode;
            arrParam[4] = new SqlParameter("@Pic", SqlDbType.VarChar);
            arrParam[4].Value = Pic;
            arrParam[5] = new SqlParameter("@Price", SqlDbType.VarChar);
            arrParam[5].Value = Price;
            arrParam[6] = new SqlParameter("@Description", SqlDbType.VarChar);
            arrParam[6].Value = Description;
            arrParam[7] = new SqlParameter("@subcategory", SqlDbType.VarChar);
            arrParam[7].Value = subcategory;
            arrParam[8] = new SqlParameter("@size", SqlDbType.VarChar);
            arrParam[8].Value = size;
            arrParam[9] = new SqlParameter("@vat", SqlDbType.VarChar);
            arrParam[9].Value = vat;
            arrParam[10] = new SqlParameter("@fularge", SqlDbType.VarChar);
            arrParam[10].Value = fularge;
            Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "[total].[TS_InsertProduct]", arrParam);


        }
        public static void SendUserDetails(string name,string To_Email_ID, string password,string location)
        {
            string host = HttpContext.Current.Request.Url.Host;
            string content = "<div style=\"margin-left:20px;\">" +
                             "<span style=\"font-size: 12px; font-family: 'CENTURY GOTHIC'\">" +
                             "<br>Dear <b>" + name + "</b>,<br><br>" +
                             "Welcome to <b>TOTAL SPORT </b><br><br>" +
                             "We are pleased to welcome you to Total Sport. Your account details are as mentioned below." +
                              "<br>UserID: " + To_Email_ID + "<br>Password: " + password + "<br>Location: " + location  +
                             "<br> Please change your password after login and keep this safely." +
                             "<br><br>Best Regards,<br>TOTAL SPORT Team" +
                             "<br><a href=\"http://www.totalsportsindia.in/\">www.totalsportsindia.in/</a></span><br><br><img src=\"cid:companylogo\" height=\"60px\" width=\"160px\"></div>";
            string Subject = "User Login Details";
            BAL.Common.SendEmail("", To_Email_ID, Subject, content, 0);

        }
        public static void RegisterLink(string To_Email_ID, string UserId, string V_str, string Name)
        {
            if (Name.Trim() == "") { Name = "User"; }
            if (Name != "") { Name = "<b>" + Name + "</b>"; }
            string host = HttpContext.Current.Request.Url.Host + "";
            string content = "<div style=\"margin-left:20px;\">" +
                             "<span style=\"font-size: 12px; font-family: 'CENTURY GOTHIC'\">" +
                             "<br>Dear " + Name + ",<br><br>" +
                             "Warm Welcome from <b>Total Sport Team!!!</b><br> We thank you for showing continued interest in CTEA INDIA.<br>" +
                             "You are just one step away from becoming a registered member." +
                             "<br>Kindly click on the link below for email id verification :" +
                             "<br><br>url: <a href=\"http://" + host + "/total1/Confirmation.aspx?id=" +
                             UserId + "&code=" + V_str + " \">Confirm Now</a>" +
                            "<br><br>Best Regards,<br>TOTAL SPORT Team" +
                             "<br><a href=\"http://www.totalsportsindia.in/\">www.totalsportsindia.in/</a></span><br><br><img src=\"cid:companylogo\" height=\"60px\" width=\"160px\"></div>";

            string Subject = "Confirmation mail From Total Sport Team";
            //LinkedResource imagelink = new LinkedResource(HttpContext.Current.Server.MapPath("images/logo.jpg"));
            //imagelink.ContentId = "imageId";
            //imagelink.TransferEncoding = System.Net.Mime.TransferEncoding.Base64;

            BAL.Common.SendEmail("", To_Email_ID, Subject, content, 0);
        }
        public static void PopulateCategBory(ref DropDownList ddlCategory)
        {
            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, "Ts_GetCategoryList");
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    ddlCategory.Items.Clear();
                    ddlCategory.DataSource = ds.Tables[0];

                    ddlCategory.DataTextField = "Category";
                    ddlCategory.DataValueField = "Category";
                    ddlCategory.DataBind();
                    ListItem lst = new ListItem("----Select Category----", "-1");
                    ddlCategory.Items.Insert(0, lst);
                }
                else
                {
                    ddlCategory.Items.Clear();
                    //ListItem lst = new ListItem("----Select category----", "-1");
                    //ddlLocation.Items.Insert(0, lst);
                }
            }
        }

        public static void PopulateSubCategBory(ref DropDownList ddlsubCategory, string category)
        {
            SqlParameter[] arrParam = new SqlParameter[1];

            arrParam[0] = new SqlParameter("@category", SqlDbType.VarChar);
            arrParam[0].Value = category;

            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, "Ts_GetSubCategoryList", arrParam);
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    ddlsubCategory.Items.Clear();
                    ddlsubCategory.DataSource = ds.Tables[0];

                    ddlsubCategory.DataTextField = "SubCategory";
                    ddlsubCategory.DataValueField = "SubCategory";
                    ddlsubCategory.DataBind();
                    ListItem lst = new ListItem("----Select SubCategory----", "-1");
                    ListItem lst1 = new ListItem("--ALL Subcategory--", "1");
                    ddlsubCategory.Items.Insert(0, lst);
                    ddlsubCategory.Items.Insert(1, lst1);
                }
                else
                {
                    ddlsubCategory.Items.Clear();
                    //ListItem lst = new ListItem("----Select category----", "-1");
                    //ddlLocation.Items.Insert(0, lst);
                }
            }
        }

        public static void populateParticularname(ref DropDownList ddlParticularName, string category, string subcategory)
        {
            SqlParameter[] arrParam = new SqlParameter[2];

            arrParam[0] = new SqlParameter("@subcategory", SqlDbType.VarChar);
            arrParam[0].Value = subcategory;
            arrParam[1] = new SqlParameter("@category", SqlDbType.VarChar);
            arrParam[1].Value = category;

            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, "Ts_GetParticularsname", arrParam);
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    ddlParticularName.Items.Clear();
                    ddlParticularName.DataSource = ds.Tables[0];
                    ddlParticularName.DataTextField = "Name";
                    ddlParticularName.DataValueField = "Name";
                    ddlParticularName.DataBind();
                    ListItem lst = new ListItem("----Select Particulars Name----", "-1");
                    ddlParticularName.Items.Insert(0, lst);
                    ListItem lst1 = new ListItem("--ALL Particular Name--", "1");
                   
                     ddlParticularName.Items.Insert(1, lst1);

                }
                else
                {
                    ddlParticularName.Items.Clear();

                    ListItem lst = new ListItem("No Particular Name for Selected Criteria", "-1");
                    ddlParticularName.Items.Insert(0, lst);
                }
            }
        }

        public static void populateBrandName(ref DropDownList ddlBrandName, string category, string subcategory, string particularName)
        {
            SqlParameter[] arrParam = new SqlParameter[3];

            arrParam[0] = new SqlParameter("@subcategory", SqlDbType.VarChar);
            arrParam[0].Value = subcategory;
            arrParam[1] = new SqlParameter("@category", SqlDbType.VarChar);
            arrParam[1].Value = category;
            arrParam[2] = new SqlParameter("@pname", SqlDbType.VarChar);
            arrParam[2].Value = particularName;

            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, "TS_GetBrandName", arrParam);
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    ddlBrandName.Items.Clear();
                    ddlBrandName.DataSource = ds.Tables[0];

                    ddlBrandName.DataTextField = "BrandName";
                    ddlBrandName.DataValueField = "BrandName";
                    ddlBrandName.DataBind();
                    ListItem lst = new ListItem("----Select Brand Name----", "-1");
                    ddlBrandName.Items.Insert(0, lst);
                    ListItem lst1 = new ListItem("--ALL brand Name--", "1");
                    ddlBrandName .Items.Insert(1, lst1);

                }
                else
                {
                    ddlBrandName.Items.Clear();

                    ListItem lst = new ListItem("No Brand for Selected Criteria", "-1");
                    ddlBrandName.Items.Insert(0, lst);
                }
            }

        }

        public static void populateCodePrice(ref DropDownList ddlItemCode, ref DropDownList ddlPrice, string category, string subcategory, string particularName, string brandname)
        {
            SqlParameter[] arrParam = new SqlParameter[4];

            arrParam[0] = new SqlParameter("@subcategory", SqlDbType.VarChar);
            arrParam[0].Value = subcategory;
            arrParam[1] = new SqlParameter("@category", SqlDbType.VarChar);
            arrParam[1].Value = category;
            arrParam[2] = new SqlParameter("@pname", SqlDbType.VarChar);
            arrParam[2].Value = particularName;
            arrParam[3] = new SqlParameter("@brandname", SqlDbType.VarChar);
            arrParam[3].Value = brandname;


            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, "TS_GetCodePrice", arrParam);
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    ddlItemCode.Items.Clear();
                    ddlItemCode.DataSource = ds.Tables[0];

                    ddlItemCode.DataTextField = "ItemCode";
                    ddlItemCode.DataValueField = "ItemCode";
                    ddlItemCode.DataBind();

                    if ((ddlItemCode.Items.Count == 1) && (ddlItemCode.SelectedValue == ""))
                    {
                        ddlItemCode.Items.Clear();
                        ListItem lst = new ListItem("No Item CodeAvailable", "-1");
                        ddlItemCode.Items.Insert(0, lst);
                    }




                    ddlPrice.DataSource = ds.Tables[0];

                    ddlPrice.DataTextField = "Price";
                    ddlPrice.DataValueField = "Price";
                    ddlPrice.DataBind();
                    //ListItem lst = new ListItem("----Select Item Code Name----", "-1");
                    //ddlItemCode.Items.Insert(0, lst);

                }
                else
                {
                    ddlItemCode.Items.Clear();

                    ListItem lst = new ListItem("No ItemCodeAvailable", "-1");
                    ddlItemCode.Items.Insert(0, lst);
                }
            }


        }

        public static int GetRefNumber()
        {
            int val = Convert.ToInt32(Total_Sport.DAL.SqlHelper.ExecuteScalar(con, "TS_GetRefNo"));
            return val;
        }

        public static void InsertQuoteDetails(int refno, string category, string subcategory,
            string particularname, string brandname, string itemcode, string price,decimal dsctPrice,
            decimal finalpricetxt, int advance, decimal discount, int subid)
        {
            SqlParameter[] arrParam = new SqlParameter[12];
            //arrParam[0] = new SqlParameter("@toName", SqlDbType.VarChar);
            //arrParam[0].Value = toName;
            arrParam[0] = new SqlParameter("@refno", SqlDbType.Int);
            arrParam[0].Value = refno;
            arrParam[1] = new SqlParameter("@category", SqlDbType.VarChar);
            arrParam[1].Value = category;
            arrParam[2] = new SqlParameter("@subcategory", SqlDbType.VarChar);
            arrParam[2].Value = subcategory;
            arrParam[3] = new SqlParameter("@particularname", SqlDbType.VarChar);
            arrParam[3].Value = particularname;
            arrParam[4] = new SqlParameter("@brandname", SqlDbType.VarChar);
            arrParam[4].Value = brandname;
            arrParam[5] = new SqlParameter("@itemcode", SqlDbType.VarChar);
            arrParam[5].Value = itemcode;
            arrParam[6] = new SqlParameter("@price", SqlDbType.VarChar);
            arrParam[6].Value = price;
            arrParam[7] = new SqlParameter("@dsctPrice", SqlDbType.Decimal);
            arrParam[7].Value = dsctPrice;
            //arrParam[9] = new SqlParameter("@EmaildId", SqlDbType.VarChar);
            //arrParam[9].Value = EmaildId;
            //arrParam[10] = new SqlParameter("@companyName", SqlDbType.VarChar);
            //arrParam[10].Value = companyname;
            arrParam[8] = new SqlParameter("@FinalPrice", SqlDbType.Decimal);
            arrParam[8].Value = finalpricetxt;
            arrParam[9] = new SqlParameter("@advance", SqlDbType.Int);
            arrParam[9].Value = advance;
            //arrParam[13] = new SqlParameter("@dlvywithin", SqlDbType.Int);
            //arrParam[13].Value = dlvywithin;
            //arrParam[14] = new SqlParameter("@dlvyrCharge", SqlDbType.Int);
            //arrParam[14].Value = dlvycharge;
            //arrParam[15] = new SqlParameter("@location", SqlDbType.VarChar);
            //arrParam[15].Value = location;
            arrParam[10] = new SqlParameter("@discountrate", SqlDbType.Decimal);
            arrParam[10].Value = discount;
            arrParam[11] = new SqlParameter("@subid", SqlDbType.Int );
            arrParam[11].Value = subid;

            Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_InsertQuote", arrParam);
        }

        public static DataSet getQutes(int refNo)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@refNo", SqlDbType.Int);
            arrParam[0].Value = refNo;
            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "TS_Getquotesrepeater", arrParam);
            return ds;

        }

        public static DataSet Getallquotes(string location)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@location", SqlDbType.VarChar);
            arrParam[0].Value = location;

            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "TS_GetQuoteGridvw",arrParam );
            return ds;
        }

        public static void InsertQuotNote(bool vat, bool advane, bool deliverycharge, bool UrgentDay,
            bool Chquename, bool Validity, bool octroi, int refno,
            int advamt,int dlvrychargeamt,int dlvrywitinnamt,int validtydayamt)
        {
            SqlParameter[] arrParam = new SqlParameter[12];
            arrParam[0] = new SqlParameter("@vat", SqlDbType.Bit);
            arrParam[0].Value = vat;
            arrParam[1] = new SqlParameter("@advane", SqlDbType.Bit);
            arrParam[1].Value = advane;
            arrParam[2] = new SqlParameter("@deliverycharge", SqlDbType.Bit);
            arrParam[2].Value = deliverycharge;
            arrParam[3] = new SqlParameter("@UrgentDay", SqlDbType.Bit);
            arrParam[3].Value = UrgentDay;
            arrParam[4] = new SqlParameter("@Chquename", SqlDbType.Bit);
            arrParam[4].Value = Chquename;
            arrParam[5] = new SqlParameter("@Validity", SqlDbType.Bit);
            arrParam[5].Value = Validity;
            arrParam[6] = new SqlParameter("@octroi", SqlDbType.Bit);
            arrParam[6].Value = octroi;
            arrParam[7] = new SqlParameter("@refno", SqlDbType.Int);
            arrParam[7].Value = refno;
            arrParam[8] = new SqlParameter("@advamt", SqlDbType.Int);
            arrParam[8].Value = advamt;
            arrParam[9] = new SqlParameter("@dlvrychrgamt", SqlDbType.Int);
            arrParam[9].Value = dlvrychargeamt;
            arrParam[10] = new SqlParameter("@dlvrywitinnamt", SqlDbType.Int);
            arrParam[10].Value = dlvrywitinnamt;
            arrParam[11] = new SqlParameter("@validtydayamt", SqlDbType.Int);
            arrParam[11].Value = validtydayamt;

            Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_InsertQuoteNote", arrParam);




        }

        public static string[] getquotepersonald(int refid)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@refid", SqlDbType.Int);
            arrParam[0].Value = refid;

            SqlDataReader sdr = Total_Sport.DAL.SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "TS_GetPersonalDtails", arrParam);
            int count = sdr.FieldCount;
            string[] details = new string[count];
            while (sdr.Read())
            {
                for (int i = 0; i < count; i++)
                {
                    details[i] = sdr[i].ToString();
                }
            }
            sdr.Close();
            return details;
        }

        public static int UpdatedsctPrice(decimal dsctrate, decimal newprice, int id)
        {
            SqlParameter[] arrParam = new SqlParameter[3];
            arrParam[0] = new SqlParameter("@dsctrate", SqlDbType.Decimal);
            arrParam[0].Value = dsctrate;
            arrParam[1] = new SqlParameter("@newprice", SqlDbType.Decimal);
            arrParam[1].Value = newprice;
            arrParam[2] = new SqlParameter("@id", SqlDbType.Int);
            arrParam[2].Value = id;

            int val = Convert.ToInt32(Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_updatediscountprice", arrParam));
            return val;

        }

        public static int Calculatefinalprice(int refno)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@refno", SqlDbType.Int);
            arrParam[0].Value = refno;
            int val = Convert.ToInt32(Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_CalculatefinalPrice", arrParam));
            return val;
        }

        public static string[] getNotes(int refid)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@refid", SqlDbType.Int);
            arrParam[0].Value = refid;

            SqlDataReader sdr = Total_Sport.DAL.SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "TS_Getnotes", arrParam);
            int count = sdr.FieldCount;
            string[] details = new string[count];
            while (sdr.Read())
            {
                for (int i = 0; i < count; i++)
                {
                    details[i] = sdr[i].ToString();
                }
            }
            sdr.Close();
            return details;
        }

        public static DataSet getProduct(string category, string subcategory, string particularname, string brandname)
        {
            SqlParameter[] arrParam = new SqlParameter[4];
            arrParam[0] = new SqlParameter("@category", SqlDbType.VarChar);
            arrParam[0].Value = category;
            arrParam[1] = new SqlParameter("@subcategory", SqlDbType.VarChar);
            arrParam[1].Value = subcategory;
            arrParam[2] = new SqlParameter("@particularname", SqlDbType.VarChar);
            arrParam[2].Value = particularname;
            arrParam[3] = new SqlParameter("@brandname", SqlDbType.VarChar);
            arrParam[3].Value = brandname;



            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "TS_GetProdctforGrdvw", arrParam);
            return ds;

        }

        public static string getSubcateryName(int refid)
        {
            SqlParameter[] arrParam = new SqlParameter[3];
            arrParam[0] = new SqlParameter("@refid", SqlDbType.Int);
            arrParam[0].Value = refid;
            string val = Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_GetSubCategory", arrParam).ToString();

            return val;


        }

        public static string[] getfinalpriceforhtml(int referenceno)
        {
            SqlParameter[] arrParam = new SqlParameter[1];

            arrParam[0] = new SqlParameter("@referenceno", SqlDbType.Int);
            arrParam[0].Value = referenceno;

            SqlDataReader sdr = Total_Sport.DAL.SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "TS_GetFinalPricehtml", arrParam);

            int count = sdr.FieldCount;
            string[] details = new string[count];

            while (sdr.Read())
            {
                for (int i = 0; i < count; i++)
                {
                    details[i] = sdr[i].ToString();

                }
            }
            sdr.Close();
            return details;
        }

        public static DataSet getallProducts()
        {
            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "TS_Getallproudcts");
            return ds;
        }

        public static void Deleteproduct(int ProductId)
        {
            SqlParameter[] arrParam = new SqlParameter[1];

            arrParam[0] = new SqlParameter("@quoteid", SqlDbType.Int);
            arrParam[0].Value = ProductId;

            Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_removeproduct", arrParam);


        }

        public static void PopulateLocation(ref DropDownList ddllocation)
        {
            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "TS_GetLocationMaster");
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    ddllocation.DataSource = ds.Tables[0];
                    ddllocation.DataTextField = "LocationName";
                    ddllocation.DataValueField = "LocationName";
                    ddllocation.DataBind();
                }
            }

        }

        public static int verifyuser(string username, string strpas, string location)
        {
            SqlParameter[] arrparam = new SqlParameter[3];
            arrparam[0] = new SqlParameter("@username", SqlDbType.VarChar, 150);
            arrparam[0].Value = username;
            arrparam[1] = new SqlParameter("@password", SqlDbType.VarChar, 150);
            arrparam[1].Value = strpas;
            arrparam[2] = new SqlParameter("@Location", SqlDbType.VarChar, 20);
            arrparam[2].Value = location;

            int val = Convert.ToInt16(Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_SelectLoginUser",arrparam));
            return val;
        }

        public static int InsertUserDetails(string username, string password, string mobileno, string address, string location, string firstname,string lastname)
        {

            SqlParameter[] arrparam = new SqlParameter[7];
            arrparam[0] = new SqlParameter("@username", SqlDbType.VarChar, 150);
            arrparam[0].Value = username;
            arrparam[1] = new SqlParameter("@password", SqlDbType.VarChar, 150);
            arrparam[1].Value = password;
            arrparam[2]=new SqlParameter("@mobileno",SqlDbType.VarChar,10);
            arrparam[2].Value = mobileno;
            arrparam[3] = new SqlParameter("@address", SqlDbType.VarChar);
            arrparam[3].Value = address;
            arrparam[4] = new SqlParameter("@location", SqlDbType.VarChar,20);
            arrparam[4].Value = location;
            arrparam[5] = new SqlParameter("@firstname", SqlDbType.VarChar, 150);
            arrparam[5].Value = firstname;
            arrparam[6] = new SqlParameter("@lastname", SqlDbType.VarChar, 150);
            arrparam[6].Value = lastname;

            int UserId=Convert.ToInt32( Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_InsertUserDetails", arrparam));
            return UserId;
        }
        public static int ChangePasswordDetails(string oldpasword,string newpasword,  int userid)
        {
            SqlParameter[] arrparam = new SqlParameter[3];
            arrparam[0] = new SqlParameter("@oldpassword", SqlDbType.VarChar, 150);
            arrparam[0].Value = oldpasword;
            arrparam[1] = new SqlParameter("@newpassword", SqlDbType.VarChar, 150);
            arrparam[1].Value = newpasword;

            arrparam[2] = new SqlParameter("@userId", SqlDbType.Int);
            arrparam[2].Value = userid;

            int value = Convert.ToInt32(Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_UpdateOldPassword", arrparam));
            return value;
        }
        public static void ConfirmUser(int userid, string Verifi_Code)
        {
            SqlParameter[] arrParam = new SqlParameter[2];
            arrParam[0] = new SqlParameter("@UId", SqlDbType.Int);
            arrParam[0].Value = userid;
            arrParam[1] = new SqlParameter("@Verifi_Str", SqlDbType.VarChar);
            arrParam[1].Value = Verifi_Code;
            Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_ConfirmUser", arrParam);
        }

        public static int OnForgotPassword(string username)
        {
            int intReturnValue = 0;
            int val = verifyuser(username, "", "");


            if (!(val.Equals(-2)))
            {
                string pass = "";
                string Info = "";
                string strName = "User";
                string emailID = "";

                SqlParameter[] arrParam = new SqlParameter[1];
                arrParam[0] = new SqlParameter("@username", SqlDbType.VarChar);
                arrParam[0].Value = username;
                //arrParam[1] = new SqlParameter("@UserId", SqlDbType.Int);
                //arrParam[1].Value = userId;

                //arrParam[1] = new SqlParameter("@type", SqlDbType.Int);
                //arrParam[1].Value = type;


                DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "TS_ForgotPassword", arrParam);
                pass = ds.Tables[0].Rows[0]["Password"].ToString();
                strName = ds.Tables[0].Rows[0]["Firstname"].ToString();
                emailID = ds.Tables[0].Rows[0]["Username"].ToString();

                if (pass != "")
                {
                    pass = BAL.Encryption.Decrypt(pass, "&%#@?,:*");
                    Info = "<span style=\"font-size:12px;\"><br>" +
                    "Dear <b>" + strName + "</b>,<br><br>Please find the details of your TOTAL SPORT account :" +
                    "<br><br>Login Username: <b>" + username + "</b><br>Password: <b>" + pass + "</b><br><br>Best Regards," +
                    "<br>TOTAL_SPORT Team<br><a href=\"http://www.totalsportsindia.in/\">www.totalsportsindia.in/</a></span><br><br><img src=\"cid:companylogo\">";
                    BAL.Common.SendEmail("", emailID, "Password Recovery", Info);
                    intReturnValue = 1;
                }
            }                        
            return intReturnValue;
        }

        public static int shifttemp(int refno)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@refno", SqlDbType.Int );
            arrParam[0].Value = refno;
            int reffno=Convert.ToInt32(Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_TempToQuote",arrParam));
            return reffno;
        }

        public static void insertpersonaldetails(string prefixname, string name, string emailid, string contn, string companynm, string loca, int refnum,int userid)
        {
            SqlParameter[] arrparam = new SqlParameter[8];
            arrparam[0] = new SqlParameter("@prefixname", SqlDbType.VarChar);
            arrparam[0].Value = prefixname;
            arrparam[1] = new SqlParameter("@name", SqlDbType.VarChar);
            arrparam[1].Value = name;
            arrparam[2] = new SqlParameter("@emailid", SqlDbType.VarChar);
            arrparam[2].Value = emailid;
            arrparam[3] = new SqlParameter("@contn", SqlDbType.VarChar);
            arrparam[3].Value = contn;
            arrparam[4] = new SqlParameter("@companynm", SqlDbType.VarChar);
            arrparam[4].Value = companynm;
            arrparam[5] = new SqlParameter("@loca", SqlDbType.VarChar);
            arrparam[5].Value = loca;
            arrparam[6] = new SqlParameter("@refnum", SqlDbType.Int);
            arrparam[6].Value = refnum;
            arrparam[7] = new SqlParameter("@userid", SqlDbType.Int);
            arrparam[7].Value = userid;

Total_Sport.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "TS_InsertpersonalDetails", arrparam);
   

        }

        public static string[] getpernldetail(int referenceno)
        {
            SqlParameter[] arrParam = new SqlParameter[1];

            arrParam[0] = new SqlParameter("@refid", SqlDbType.Int);
            arrParam[0].Value = referenceno;

            SqlDataReader sdr = Total_Sport.DAL.SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "TS_GetPersonalDtails", arrParam);

            int count = sdr.FieldCount;
            string[] details = new string[count];

            while (sdr.Read())
            {
                for (int i = 0; i < count; i++)
                {
                    details[i] = sdr[i].ToString();

                }
            }
            sdr.Close();
            return details;
        
        
        }
        public static DataSet getproductquote(int referenceno)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@referenceno", SqlDbType.VarChar);
            arrParam[0].Value = referenceno;

            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "TS_ProudctQuotegw", arrParam);
            return ds;
        }
        public static DataSet getproductdetails(int referenceno)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@referenceno", SqlDbType.VarChar);
            arrParam[0].Value = referenceno;

            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "TS_ShowQuoteDetalis", arrParam);
            return ds;
        }

        public static DataSet getproductquoteDetails(int referenceno)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@refNo", SqlDbType.VarChar);
            arrParam[0].Value = referenceno;

            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "TS_SelectShowQuote", arrParam);
            return ds;


        }


        public static void UpdateInvoiceColumn(int referenceno)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@referenceno", SqlDbType.VarChar);
            arrParam[0].Value = referenceno;
            Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_UpdateInvoiceColumn", arrParam);


        }


        public static DataSet getproducinvoicegw(int referenceno)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@referenceno", SqlDbType.VarChar);
            arrParam[0].Value = referenceno;



            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "TS_GetPRoductinvoicegw", arrParam);
            return ds;


        }


        public static int InsertTemplateDetails(string templatename, int tid, string name,
            string Companyname, string emailid, string contactno, string location, 
            bool VAT, bool advance, bool delivarych, bool deliveryin15day, bool cheqname,
            bool validitydays, bool priceinsurance, string category, string subcategory, 
            string particularName, string brandname, int discountrate, int advanceamt, 
            int deliverychargesamt, int deliverywithinamt, int validityamt,int refno)

        {
            SqlParameter[] arrparam = new SqlParameter[24];
            arrparam[0] = new SqlParameter("@templatename", SqlDbType.VarChar);
            arrparam[0].Value = templatename;
            arrparam[1] = new SqlParameter("@aaa", SqlDbType.Int);
            arrparam[1].Value = tid;
            arrparam[2] = new SqlParameter("@name", SqlDbType.VarChar);
            arrparam[2].Value = name;
            arrparam[3] = new SqlParameter("@Companyname", SqlDbType.VarChar);
            arrparam[3].Value = Companyname;
            arrparam[4] = new SqlParameter("@emailid", SqlDbType.VarChar);
            arrparam[4].Value = emailid;
            arrparam[5] = new SqlParameter("@contactno", SqlDbType.VarChar);
            arrparam[5].Value = contactno;
            arrparam[6] = new SqlParameter("@location", SqlDbType.VarChar);
            arrparam[6].Value = location;
            arrparam[7] = new SqlParameter("@VAT", SqlDbType.Bit);
            arrparam[7].Value = VAT;
            arrparam[8] = new SqlParameter("@advance", SqlDbType.Bit);
            arrparam[8].Value = advance;
            arrparam[9] = new SqlParameter("@delivarych", SqlDbType.Bit);
            arrparam[9].Value = delivarych;
            arrparam[10] = new SqlParameter("@deliveryin15day", SqlDbType.Bit);
            arrparam[10].Value = deliveryin15day;
            arrparam[11] = new SqlParameter("@cheqname", SqlDbType.Bit);
            arrparam[11].Value = cheqname;
            arrparam[12] = new SqlParameter("@validitydays", SqlDbType.Bit);
            arrparam[12].Value = validitydays;
            arrparam[13] = new SqlParameter("@priceinsurance", SqlDbType.Bit);
            arrparam[13].Value = priceinsurance;
            arrparam[14] = new SqlParameter("@category", SqlDbType.VarChar);
            arrparam[14].Value = category;
            arrparam[15] = new SqlParameter("@subcategory", SqlDbType.VarChar);
            arrparam[15].Value = subcategory;
            arrparam[16] = new SqlParameter("@particularName", SqlDbType.VarChar);
            arrparam[16].Value = particularName;
            arrparam[17] = new SqlParameter("@brandname", SqlDbType.VarChar);
            arrparam[17].Value = brandname;
            arrparam[18] = new SqlParameter("@discountrate", SqlDbType.Int);
            arrparam[18].Value = discountrate;
            arrparam[19] = new SqlParameter("@advanceamt", SqlDbType.Int);
            arrparam[19].Value = advanceamt;
            arrparam[20] = new SqlParameter("@deliverychargesamt", SqlDbType.Int);
            arrparam[20].Value = deliverychargesamt;
            arrparam[21] = new SqlParameter("@deliverywithinamt", SqlDbType.Int);
            arrparam[21].Value = deliverywithinamt;
            arrparam[22] = new SqlParameter("@validityamt", SqlDbType.Int);
            arrparam[22].Value = validityamt;
            arrparam[23] = new SqlParameter("@refno", SqlDbType.Int);
            arrparam[23].Value = refno;



            int val=Convert.ToInt32( Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_InsertTemplateName", arrparam));
            return val;
        }



        public static void PopulateTemplateName(ref DropDownList ddltemplate)
        {
            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "TS_GetTemplateName");
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    ddltemplate.DataSource = ds.Tables[0];
                    ddltemplate.DataTextField = "tName";
                    ddltemplate.DataValueField = "tName";
                   
                    ddltemplate.DataBind();

                }
                ListItem lst = new ListItem("----Select Template----", "-1");
                ddltemplate.Items.Insert(0, lst);
            }

        }


        public static string[] SelectTemplateDetails(string templatename)
        {
            SqlParameter[] arrParam = new SqlParameter[1];

            arrParam[0] = new SqlParameter("@templateName", SqlDbType.VarChar);
            arrParam[0].Value = templatename;

            SqlDataReader sdr = Total_Sport.DAL.SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "TS_SelectTemplateDetails", arrParam);

            int count = sdr.FieldCount;
            string[] details = new string[count];

            while (sdr.Read())
            {
                for (int i = 0; i < count; i++)
                {
                    details[i] = sdr[i].ToString();

                }
            }
            sdr.Close();
            return details;


        }


        public static void getinvoicedtil(int referenceno)
        {
        }
         public static DataSet ShowSearchedData(string QuoteID ,string	RecipientName,string CompanyName)
        {

            SqlParameter[] arrParam = new SqlParameter[3];

            arrParam[0] = new SqlParameter("@QuoteID", SqlDbType.VarChar);
            arrParam[0].Value = QuoteID;
            arrParam[1] = new SqlParameter("@RecipientName", SqlDbType.VarChar);
            arrParam[1].Value = RecipientName;
            arrParam[2] = new SqlParameter("@CompanyName", SqlDbType.VarChar);
            arrParam[2].Value = CompanyName;

            DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "TS_ShowSearchedData", arrParam);
            return ds;

        
        }



         public static void insertinvoicedetl(int personalid, int subid, int qnty, int finalpirce, int refno)
         {

             SqlParameter[] arrparam = new SqlParameter[5];
             arrparam[0] = new SqlParameter("@personalid", SqlDbType.Int);
             arrparam[0].Value = personalid;
             arrparam[1] = new SqlParameter("@subid", SqlDbType.Int);
             arrparam[1].Value = subid;
             arrparam[2] = new SqlParameter("@qnty", SqlDbType.Int);
             arrparam[2].Value = qnty;
             arrparam[3] = new SqlParameter("@finalpirce", SqlDbType.Int);
             arrparam[3].Value = finalpirce;
             arrparam[4] = new SqlParameter("@refno", SqlDbType.Int);
             arrparam[4].Value = refno;

             Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_InsertInvoiceDetails", arrparam);
           

         }

         public static string[] getqty(int referenceno)
         {
             SqlParameter[] arrparam = new SqlParameter[1];
             arrparam[0] = new SqlParameter("@referenceno", SqlDbType.Int);
             arrparam[0].Value = referenceno;

             SqlDataReader sdr = Total_Sport.DAL.SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "TS_GetQuty", arrparam);

             int count = sdr.FieldCount;
             string[] details = new string[count];

             while (sdr.Read())
             {
                 for (int i = 0; i < count; i++)
                 {
                     details[i] = sdr[i].ToString();

                 }
             }
             sdr.Close();
             return details;


         }

         public static void Updateinvoice(int personalidd, int subid, int qnty, int finalpirce, int refno)
         {
             SqlParameter[] arrparam = new SqlParameter[5];
             arrparam[0] = new SqlParameter("@personalidd", SqlDbType.Int);
             arrparam[0].Value = personalidd;
             arrparam[1] = new SqlParameter("@ProductSubID", SqlDbType.Int);
             arrparam[1].Value = subid;
             arrparam[2] = new SqlParameter("@quantity", SqlDbType.Int);
             arrparam[2].Value = qnty;
             arrparam[3] = new SqlParameter("@FinalPrice", SqlDbType.Int);
             arrparam[3].Value = finalpirce;
             arrparam[4] = new SqlParameter("@refno", SqlDbType.Int);
             arrparam[4].Value = refno;

          Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_UpdateInvoiceDetails", arrparam);
           
         }

         public static void UpdteIvoiceaftrCreation(int refereno)
         {
             SqlParameter[] arrparam = new SqlParameter[1];
             arrparam[0] = new SqlParameter("@refereno", SqlDbType.Int);
             arrparam[0].Value = refereno;
             Total_Sport.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "TS_UpdtinvoiceAfterCreation", arrparam);
          
         }
         public static void TruncateProductDEtails()
         {
              Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_TruncateProductDEtails_Tem");

         }


         public static void insertdataintosql(string Category,string SubCategory,
             string ProductName,string BrandName,string ItemCode,string Image,string Price,
             string Description, string size, string vat, string imageLarge,int subId)
         {

             SqlParameter[] arrparam = new SqlParameter[12];
             arrparam[0] = new SqlParameter("@Category", SqlDbType.VarChar);
             arrparam[0].Value = Category;
             arrparam[1] = new SqlParameter("@SubCategory", SqlDbType.VarChar);
             arrparam[1].Value = SubCategory;
             arrparam[2] = new SqlParameter("@ProductName", SqlDbType.VarChar);
             arrparam[2].Value = ProductName;
             arrparam[3] = new SqlParameter("@BrandName", SqlDbType.VarChar);
             arrparam[3].Value = BrandName;
             arrparam[4] = new SqlParameter("@ItemCode", SqlDbType.VarChar);
             arrparam[4].Value = ItemCode;
             arrparam[5] = new SqlParameter("@Image", SqlDbType.VarChar);
             arrparam[5].Value = Image;
             arrparam[6] = new SqlParameter("@Price", SqlDbType.VarChar);
             arrparam[6].Value = Price;
             arrparam[7] = new SqlParameter("@Description", SqlDbType.VarChar);
             arrparam[7].Value = Description;
             arrparam[8] = new SqlParameter("@size", SqlDbType.VarChar);
             arrparam[8].Value = size;
             arrparam[9] = new SqlParameter("@vat", SqlDbType.VarChar);
             arrparam[9].Value = vat;
             arrparam[10] = new SqlParameter("@imageLarge", SqlDbType.VarChar);
             arrparam[10].Value = imageLarge;
             arrparam[11] = new SqlParameter("@SubId", SqlDbType.Int);
             arrparam[11].Value = subId;
             Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_GetXslProductDetails", arrparam);
    }



         public static void InsertUpdateProductDetails(string Category, string SubCategory,
              string ProductName, string BrandName, string ItemCode, string Image, string Price,
              string Description, string size, string vat, string imageLarge,int subId)
         {

             SqlParameter[] arrparam = new SqlParameter[12];
             arrparam[0] = new SqlParameter("@Category", SqlDbType.VarChar);
             arrparam[0].Value = Category;
             arrparam[1] = new SqlParameter("@SubCategory", SqlDbType.VarChar);
             arrparam[1].Value = SubCategory;
             arrparam[2] = new SqlParameter("@Name", SqlDbType.VarChar);
             arrparam[2].Value = ProductName;
             arrparam[3] = new SqlParameter("@BrandName", SqlDbType.VarChar);
             arrparam[3].Value = BrandName;
             arrparam[4] = new SqlParameter("@ItemCode", SqlDbType.VarChar);
             arrparam[4].Value = ItemCode;
             arrparam[5] = new SqlParameter("@Image", SqlDbType.VarChar);
             arrparam[5].Value = Image;
             arrparam[6] = new SqlParameter("@Price", SqlDbType.VarChar);
             arrparam[6].Value = Price;
             arrparam[7] = new SqlParameter("@Description", SqlDbType.VarChar);
             arrparam[7].Value = Description;
             arrparam[8] = new SqlParameter("@size", SqlDbType.VarChar);
             arrparam[8].Value = size;
             arrparam[9] = new SqlParameter("@vat", SqlDbType.VarChar);
             arrparam[9].Value = vat;
             arrparam[10] = new SqlParameter("@imageLarge", SqlDbType.VarChar);
             arrparam[10].Value = imageLarge;
             arrparam[11] = new SqlParameter("@SubId", SqlDbType.Int);
             arrparam[11].Value = subId;

             Total_Sport.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "TS_InsertUpdateProductDetails", arrparam);
         }
         public static void populateParticularname1(ref DropDownList ddlParticularName, string category, string subcategory)
         {
             SqlParameter[] arrParam = new SqlParameter[2];

             arrParam[0] = new SqlParameter("@subcategory", SqlDbType.VarChar);
             arrParam[0].Value = subcategory;
             arrParam[1] = new SqlParameter("@category", SqlDbType.VarChar);
             arrParam[1].Value = category;

             DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, "Ts_GetParticularsname", arrParam);
             if (ds.Tables.Count > 0)
             {
                 if (ds.Tables[0].Rows.Count > 0)
                 {
                     ddlParticularName.Items.Clear();
                     ddlParticularName.DataSource = ds.Tables[0];
                     ddlParticularName.DataTextField = "Name";
                     ddlParticularName.DataValueField = "Name";
                     ddlParticularName.DataBind();
                     ListItem lst = new ListItem("----Select Particulars Name----", "-1");
                     ddlParticularName.Items.Insert(0, lst);            

                 }
                 else
                 {
                     ddlParticularName.Items.Clear();
                  
                 }
             }
         }

         public static void PopulateSubCategBory1(ref DropDownList ddlsubCategory, string category)
         {
             SqlParameter[] arrParam = new SqlParameter[1];

             arrParam[0] = new SqlParameter("@category", SqlDbType.VarChar);
             arrParam[0].Value = category;

             DataSet ds = Total_Sport.DAL.SqlHelper.ExecuteDataset(con, "Ts_GetSubCategoryList", arrParam);
             if (ds.Tables.Count > 0)
             {
                 if (ds.Tables[0].Rows.Count > 0)
                 {
                     ddlsubCategory.Items.Clear();
                     ddlsubCategory.DataSource = ds.Tables[0];

                     ddlsubCategory.DataTextField = "SubCategory";
                     ddlsubCategory.DataValueField = "SubCategory";
                     ddlsubCategory.DataBind();
                     ListItem lst = new ListItem("----Select SubCategory----", "-1");
                     ddlsubCategory.Items.Insert(0, lst);                   
                 }
                 else
                 {
                     ddlsubCategory.Items.Clear();
                     //ListItem lst = new ListItem("----Select category----", "-1");
                     //ddlLocation.Items.Insert(0, lst);
                 }
             }
         }



    }
}


